﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using cinemawcftest.DBLayer;
using System.Data;

public class DBBooking : DataBase
{
    DBTicket dbt = new DBTicket();
    /// <summary>
    /// Returns a booking object from the database
    /// </summary>
    /// <param name="id">The id of the booking object</param>
    /// <returns>Booking object</returns>
    public Booking read(int id)
    {
            SqlDataReader rdr = null;
            Booking booking = null;
            List<Ticket> tic = null;
      
            string readQuery = "Select * from Booking where ID = @id;";
            try
            {
                Connection.Open();

                SqlCommand cmd = new SqlCommand(readQuery, Connection);
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;

                rdr = cmd.ExecuteReader();
                
                while (rdr.Read())
                {
                    tic = dbt.ReadFromBookingId((int)rdr["ID"]);

                    booking = new Booking((int) rdr["ID"], (double) rdr["TotalPrice"],(string)rdr ["BookingStatus"],(string)rdr["CustomerPhone"], tic);
                }
            }
            finally
            {
                //close reader
                if (rdr != null)
                {
                    rdr.Close();
                }

                // close connection
                if (Connection != null)
                {
                    Connection.Close();
                }
            }

            return booking;
        }
    /// <summary>
    /// Returns a list of booking objects from the database
    /// </summary>
    /// <param name="customer">The Customer object</param>
    /// <returns>List of Booking object</returns>
    public List<Booking> read(Customer customer)
    {
        SqlDataReader rdr = null;
        List<Booking> booking = null;
        List<Ticket> tic = null;
        string readQuery = "Select * from Booking where CustomerPhone LIKE @customerPhone;";
        try
        {
            Connection.Open();
            
            SqlCommand cmd = new SqlCommand(readQuery, Connection);
            cmd.Parameters.Add("%@customerPhone%", SqlDbType.VarChar).Value = customer.PhoneNo;

            rdr = cmd.ExecuteReader();
            
            while (rdr.Read())
            {
                tic = dbt.ReadFromBookingId((int)rdr["ID"]);
                booking.Add(new Booking((int)rdr["ID"], (double)rdr["TotalPrice"], (string)rdr["BookingStatus"], (string)rdr["CustomerPhone"], tic));
            }
        }
        finally
        {
            //close reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // close connection
            if (Connection != null)
            {
                Connection.Close();
            }
        }

        return booking;
    }
    /// <summary>
    /// Inserts a booking object into the database
    /// </summary>
    /// <param name="customer">The Customer object</param>
    /// <returns>+1 or -1</returns>
    public int Insert(Booking booking)
    {
        int succeed = 0;

        string query = "INSERT INTO Booking(ID, BookingStatus, TotalPrice, CustomerPhone)VALUES(@ID, @bookingstatus, @totalprice, @customerphone);";

        try
        {
            SqlCommand cmd = new SqlCommand(query, Connection);
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = booking.Id;
            cmd.Parameters.Add("@bookingstatus", SqlDbType.VarChar).Value = booking.Status;
            cmd.Parameters.Add("@totalprice", SqlDbType.Float).Value = booking.TotalPrice;
            cmd.Parameters.Add("@customerphone", SqlDbType.VarChar).Value = booking.CustomerPhone;

            Connection.Open();
            succeed = cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            succeed = -1;
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            Connection.Close();
        }

        return succeed;
    }
    /// <summary>
    /// updates a booking object in the database
    /// </summary>
    /// <param name="customer">The Customer object</param>
    /// <returns>+1 or -1</returns>
    public int Update(Booking booking)
    {
        int succeed = 0;

        string query = "UPDATE Booking SET BookingStatus = @bookingstatus Where CustomerPhone = @customerphone;";
        try
        {
            SqlCommand cmd = new SqlCommand(query, Connection);
            cmd.Parameters.Add("@customerphone", SqlDbType.VarChar).Value = booking.CustomerPhone;
            cmd.Parameters.Add("@bookingstatus", SqlDbType.VarChar).Value = booking.Status;

            Connection.Open();
            succeed = cmd.ExecuteNonQuery();
        }
         catch (SqlException)  
         {
             succeed = -1;
         }
         catch (Exception)
         {
             
             throw;
         }
        finally
         {
             Connection.Close();
         }
         return succeed;
    }
    /// <summary>
    /// Returns the highest id number from the database
    /// </summary>
    /// <returns>+1 or -1</returns>
    public int BookingMaxID()
    {
        int maxID = 0;

        SqlDataReader rdr = null;
        string query = "SELECT MAX(ID) AS ID FROM BOOKING;";
        try
        {
            Connection.Open();
            
            SqlCommand cmd = new SqlCommand(query, Connection);


            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                maxID = (int)rdr["ID"];
            }
        }
        finally
        {
            //close reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // close connection
            if (Connection != null)
            {
                Connection.Close();
            }
        }
        return maxID;
    }

    public double CollectTicketPrice(Booking booking)
    {
        double sum = 0;

        foreach (Ticket obj in booking.Tickets)
        {
            sum += obj.TicketPrice;
        }
        return sum;
    }































    //private SqlConnection con;

    //public Booking book
    //{
    //    get;
    //    set;
    //}

    //public Booking BuildBooking(DataRow dr)
    //{
    //    throw new System.NotImplementedException();
    //}

    //public List<Booking> GetAllBookings()
    //{
    //    throw new System.NotImplementedException();
    //}

    //public Booking SingleWhere(string query, SQLParameter[] parameter = null)
    //{
    //    throw new System.NotImplementedException();
    //}

    //public List<Booking> MiscWhere(string query, SQLParameter[] parameter = null)
    //{
    //    throw new System.NotImplementedException();
    //}

    //public Booking GetBookingByCustomerPhone(string phone)
    //{
    //    throw new System.NotImplementedException();
    //}

}

